﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace C_sports_meet.Manager
{
    /// <summary>
    /// MVolunteer.xaml 的交互逻辑
    /// </summary>
    public partial class MVolunteer : Page
    {
        public MVolunteer()
        {
            InitializeComponent();
            DBConnection2 conn = new DBConnection2();
            DataTable dataset = conn.Select();
            if (dataset == null)
            {
                MessageBox.Show("没有数据");
            }
            DataGrid1.ItemsSource = dataset.DefaultView;
        }



        private void btnUpdate_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                DataRowView mySelectedElement = (DataRowView)DataGrid1.SelectedItem;
                //调用删除函数
                int j = Delete(mySelectedElement, mySelectedElement.Row[0].ToString());
                //调用查询函数
                int i = Insert(mySelectedElement, mySelectedElement.Row[0].ToString(), mySelectedElement.Row[1].ToString(), mySelectedElement.Row[2].ToString(), mySelectedElement.Row[3].ToString());

                if (j + i == 2)
                {
                    MessageBox.Show("修改成功");
                }
                else
                {
                    MessageBox.Show("修改失败");
                }
                DBConnection2 conn = new DBConnection2();
                DataTable dataset = conn.Select();
                if (dataset == null)
                {
                    MessageBox.Show("没有数据");
                }
                DataGrid1.ItemsSource = dataset.DefaultView;
            }
            catch
            {
                MessageBox.Show("操作失误，请重试。");
            }
        }

        private void btnDelete_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                DataRowView mySelectedElement = (DataRowView)DataGrid1.SelectedItem;
                //调用删除函数
                int j = Delete(mySelectedElement, mySelectedElement.Row[0].ToString());
                if (j == 1)
                {
                    MessageBox.Show("删除成功");
                }
                else
                {
                    MessageBox.Show("删除失败");
                }
                DBConnection2 conn = new DBConnection2();
                DataTable dataset = conn.Select();
                if (dataset == null)
                {
                    MessageBox.Show("没有数据");
                }
                DataGrid1.ItemsSource = dataset.DefaultView;
            }
            catch
            {
                MessageBox.Show("操作失误，请重试。");
            }
        }

        private void inputAno_LostFocus(object sender, RoutedEventArgs e)
        {
            if (inputAno.Text.Trim() == "")
            {
                inputAno.Text = "请输入编号";
            }
        }

        private void inputAno_GotFocus(object sender, RoutedEventArgs e)
        {
            if (inputAno.Text.Trim() == "请输入编号")
            {
                inputAno.Text = "";
            }
        }
        //删除的函数
        private int Delete(DataRowView dataRowView, string selectItem)
        {
            string strCon2 = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
            SqlConnection con2 = new SqlConnection(strCon2);
            string strSql2 = "delete  from volunteer where vno='" + selectItem + "';";
            SqlCommand cmd2 = new SqlCommand(strSql2, con2);
            con2.Open();
            int j = cmd2.ExecuteNonQuery();
            con2.Close();
            return j;
        }

        //插入的函数
        private int Insert(DataRowView dataRowView, string selectItem, string selectItem2, string selectItem3, string selectItem4)
        {
            string strCon = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
            SqlConnection con = new SqlConnection(strCon);
            string strSql = "insert into volunteer(vno,vname,vsex,vage) values( '" + selectItem + "','" + selectItem2 + "','" + selectItem3 + "','" + selectItem4 + "')";
            SqlCommand cmd = new SqlCommand(strSql, con);
            con.Open();
            int i = cmd.ExecuteNonQuery();
            con.Close();
            return i;
        }

        private void inputAname_LostFocus(object sender, RoutedEventArgs e)
        {
            if (inputAname.Text.Trim() == "")
            {
                inputAname.Text = "请输入姓名";
            }
        }

        private void inputAname_GotFocus(object sender, RoutedEventArgs e)
        {
            if (inputAname.Text.Trim() == "请输入姓名")
            {
                inputAname.Text = "";
            }
        }

        private void btnInsert_Click(object sender, RoutedEventArgs e)
        {
            DataRowView mySelectedElement = (DataRowView)DataGrid1.SelectedItem;
            string strCon = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
            SqlConnection con = new SqlConnection(strCon);
            string strSql = "";
            strSql = "select * from volunteer where vno='" + mySelectedElement.Row[0].ToString() + "'";
            SqlCommand cmd0 = new SqlCommand(strSql, con);
            con.Open();
            int count = Convert.ToInt32(cmd0.ExecuteScalar());
            con.Close();
            if (count > 0)
            {
                MessageBox.Show("请勿重复输入");
            }
            else if (mySelectedElement.Row[0].ToString() == null || mySelectedElement.Row[1].ToString() == null || mySelectedElement.Row[2].ToString() == null || mySelectedElement.Row[3].ToString() == null)
            {
                MessageBox.Show("添加项均不能为空");
            }
            else
            {
                //调用插入函数
                int i = Insert(mySelectedElement, mySelectedElement.Row[0].ToString(), mySelectedElement.Row[1].ToString(), mySelectedElement.Row[2].ToString(), mySelectedElement.Row[3].ToString());
                if (i == 1)
                {
                    MessageBox.Show("添加成功");
                }
                else
                {
                    MessageBox.Show("添加失败");
                }
            }
        }

        private void btnSelect_Click(object sender, RoutedEventArgs e)
        {
            DBConnection2 conn = new DBConnection2(inputAno.Text);
            DataTable dataset = conn.Select();
            if (dataset == null)
            {
                MessageBox.Show("没有数据");
            }
            else
            {
                MessageBox.Show("参加比赛信息查询成功（若想增添，修改，删除信息请点击恢复初始状态）");
            }
            DataGrid1.ItemsSource = dataset.DefaultView;
        }

        private void btnFind_Click(object sender, RoutedEventArgs e)
        {
            DBConnection2 conn = new DBConnection2(inputAno.Text, inputAname.Text);
            DataTable dataset = conn.Select();
            if (dataset == null)
            {
                MessageBox.Show("没有数据");
            }
            else
            {
                MessageBox.Show("检索信息成功");
            }
            DataGrid1.ItemsSource = dataset.DefaultView;
        }
        private void btnRefresh_Click(object sender, RoutedEventArgs e)
        {
            DBConnection2 conn = new DBConnection2();
            DataTable dataset = conn.Select();
            if (dataset == null)
            {
                MessageBox.Show("没有数据");
            }
            DataGrid1.ItemsSource = dataset.DefaultView;
        }
    }

    class DBConnection2
    {
        private SqlConnection connection;
        public string vno = null;
        public string vname = null;
        public DBConnection2()
        {
            try
            {
                string strCon = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
                connection = new SqlConnection(strCon);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public DBConnection2(string vno)
        {
            try
            {
                string strCon = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
                connection = new SqlConnection(strCon);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            this.vno = vno;

        }
        public DBConnection2(string vno, string vname)
        {
            try
            {
                string strCon = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
                connection = new SqlConnection(strCon);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            this.vno = vno;
            this.vname = vname;

        }
        public DataTable Select()
        {
            try
            {
                string strSql = "";
                connection.Open();
                if (vno == null)
                {
                    strSql = "select vno 编号,vname 姓名,vsex 性别,vage 年龄 from volunteer";
                    SqlCommand cmd = new SqlCommand(strSql, connection);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }
                else if (vname == null && vno != null)
                {
                    strSql = "select eno 项目号,vno 编号,vatime 比赛时间,whetherentry 是否参加 from vapply where vno='" + vno + "'";
                    SqlCommand cmd1 = new SqlCommand(strSql, connection);
                    SqlDataAdapter sda1 = new SqlDataAdapter(cmd1);
                    DataTable dt1 = new DataTable();
                    sda1.Fill(dt1);
                    return dt1;
                }
                else
                {
                    strSql = "select vno 编号,vname 姓名,vsex 性别,vage 年龄 from volunteer where vno='" + vno + "'and vname='" + vname + "'";
                    SqlCommand cmd2 = new SqlCommand(strSql, connection);
                    SqlDataAdapter sda2 = new SqlDataAdapter(cmd2);
                    DataTable dt2 = new DataTable();
                    sda2.Fill(dt2);
                    return dt2;
                }

            }
            catch (Exception es)
            {
                throw new Exception(es.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
